package com.plamform.tool;

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * 数据库连接工具,可用于对接第三方数据
 */
public class DBTools {

    private Connection connection;

    public DBTools() {
        try {
            String url, user, password = "";
            url = "jdbc:mysql://localhost:3306/jrwy";
            user = "root";
            password = "root";
            this.connection = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void executeUpdate(String sql, List<Object> params) {
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            if (params != null) {
                for (int i = 0; i < params.size(); i++) {
                    statement.setObject(i + 1, params.get(i));
                }
            }
            int rowsAffected = statement.executeUpdate();
            System.out.println("Rows affected: " + rowsAffected);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public List<List<Object>> executeQuery(String sql, List<Object> params) {
        List<List<Object>> results = new ArrayList<>();
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            if (params != null) {
                for (int i = 0; i < params.size(); i++) {
                    //按照顺序放参数对应的值
                    statement.setObject(i + 1, params.get(i));
                }
            }
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                List<Object> row = new ArrayList<>();
                //字段长度
                for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
                    //字段对应的值,存储是二进制
                    row.add(resultSet.getObject(i));
                    //row.add(resultSet.getInt(i));
                    //row.add(resultSet.getString(i));
                }
                results.add(row);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return results;
    }

    public void close() {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        DBTools dbTools = new DBTools();

        // Insert example
        //insert(dbTools);

        // Update example
        //update(dbTools);

        // Delete example
        //delete(dbTools);

        // Query example
        //查询可以根据条件、不需要条件查询
        query( dbTools);
        dbTools.close();
    }

    static void delete(DBTools dbTools) {
        //String deleteSql = "DELETE FROM sys_dept WHERE dept_id = ?";
        String deleteSql2 = "DELETE FROM sys_dept ";
        //List<Object> deleteParams = new ArrayList<>();
        List<Object> deleteParams2 = new ArrayList<>();
        // deleteParams.add("200");
        //dbTools.executeUpdate(deleteSql, deleteParams);
        dbTools.executeUpdate(deleteSql2, null);
    }

    static void update(DBTools dbTools) {
        String updateSql = "UPDATE sys_dept SET dept_name = ? WHERE dept_id = ?";
        List<Object> updateParams = new ArrayList<>();
        updateParams.add("深圳打总公司");
        updateParams.add("101");
        dbTools.executeUpdate(updateSql, updateParams);
    }

    static void insert(DBTools dbTools) {
        String insertSql = "INSERT INTO sys_dept (dept_name, order_num) VALUES (?, ?)";
        List<Object> insertParams = new ArrayList<>();
        insertParams.add("开源部");
        insertParams.add("43");
        dbTools.executeUpdate(insertSql, insertParams);
    }

    static void query(DBTools dbTools) {
        //sql
        String querySql = "SELECT * FROM sys_dept WHERE dept_id = ?";
        String querySql2 = "SELECT * FROM sys_dept";
        //参数
        List<Object> queryParams = new ArrayList<>();
        //List<Object> queryParams2 = new ArrayList<>();
        queryParams.add("101");
        List<List<Object>> queryResults = dbTools.executeQuery(querySql, queryParams);
        List<List<Object>> queryResults2 = dbTools.executeQuery(querySql2, null);
        for (List<Object> row : queryResults) {
            System.out.println(row);
        }
        System.out.println("queryResults2 = " + queryResults2);
    }


}
